 -- Modo RAW
USE AdventureWorks
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW

 -- ELEMENTS com modo RAW
USE AdventureWorks
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW, ELEMENTS

-- Elemento nomeado no modo RAW
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW('Product')

-- elemento raiz
SELECT ProductID, Name, ListPrice
FROM Production.Product
FOR XML RAW, ROOT('Products')

-- Ingressar no modo RAW
SELECT Category.Name CategoryName, Product.ProductID, Product.Name
FROM Production.ProductSubCategory Category
JOIN Production.Product Product
ON Product.ProductSubCategoryID = Category.ProductSubCategoryID
ORDER BY Category.Name, Product.ProductID
FOR XML RAW

-- Modo AUTO
SELECT ProductID, Name, ListPrice
FROM Production.Product Product
FOR XML AUTO

-- Ingressar no modo AUTO
SELECT Category.Name CategoryName, Product.ProductID, Product.Name
FROM Production.ProductSubCategory Category
JOIN Production.Product Product
ON Product.ProductSubCategoryID = Category.ProductSubCategoryID
ORDER BY Category.Name, Product.ProductID
FOR XML AUTO

-- XML aninhado com TYPE
SELECT ProductID, 
       Name, ListPrice,
       (SELECT ReviewerName, Comments
        FROM Production.ProductReview ProductReview
        WHERE ProductReview.ProductID = Product.ProductID
        FOR XML AUTO, ELEMENTS, TYPE)
FROM Production.Product Product
WHERE ProductID > 795
FOR XML AUTO

-- Modo EXPLICIT
SELECT	1 AS TAG,
		NULL AS Parent,
		ProductID AS [Product!1!ProductID],
		Name AS [Product!1]
FROM Production.Product
FOR XML EXPLICIT

-- Aninhamento no modo EXPLICIT
SELECT	1 AS TAG,
		NULL AS Parent,
		ProductID AS [Product!1!ProductID],
		Name AS [Product!1!ProductName!Element],
		NULL AS [Review!2!Reviewer],
		NULL AS [Review!2]
FROM Production.Product
WHERE ProductID > 795
UNION ALL
SELECT	2,
		1,
		p.ProductID,
		NULL,
		r.ReviewerName AS [Review!2!Reviewer],
		r.Comments AS [Review!2]
FROM Production.ProductReview r
JOIN Production.Product p
ON  p.ProductID = r.ProductID
WHERE p.ProductID > 795
ORDER BY [Product!1!ProductID]
FOR XML EXPLICIT

-- Modo PATH
SELECT ProductID AS "@ProductID",
       Name AS "*",
       Size AS "Description/@Size",
       Color AS "Description/text()"
FROM Production.Product
ORDER BY Name
FOR XML PATH('Product')


